package com.jdbccrud;

import com.entity.Student;
import com.mysql.cj.xdevapi.Table;
import com.utils.JDBCUtils;
import org.junit.Test;

import javax.swing.plaf.synth.SynthUI;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Scanner;

/**
 * @author wzm
 * @create 2023-06-13 10:37
 */
public class CRUDTest {
   private Connection conn = null;
   private  PreparedStatement ps = null;
   private  ResultSet rs = null;

    //新增
    @Test
    public void insert(){
        try {
            //1.获取与数据库的连接
            conn = JDBCUtils.getConn();

            //2.定义需要发给数据库执行的sql语句 使用问号占位
            String sql = "insert into student values(?,?,?,?,?,?)";

            //3.获取执行sql的对象
            //- Statement statement = conn.createStatement(); 不建议使用 会有sql注入问题
            //推荐使用 结合问号占位  然后 将sql预先编译 不能再通过字符串修改了  只能走内部的赋值
            ps = conn.prepareStatement(sql);

            //3.1为问号赋值 通过ps的方法
            //参数1 表示赋值参数的问号的第几个  值
            ps.setInt(1,0); //id
            ps.setString(2,"测试数据");//username
            ps.setInt(3,18);//age
            ps.setString(4,"大理"); //address
            ps.setInt(5,11); //ordered
            ps.setInt(6,11);//status

            //4.执行sql返回执行的结果  executeUpdate 执行增删改  返回受影响行数
            int i = ps.executeUpdate();

            //5.对结果进行处理
            if(i > 0){
                System.out.println("新增成功");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //始终关闭资源
            JDBCUtils.closeRes(conn,ps);
        }

    }

    //删除
    @Test
    public void delete(){
        try {
            conn = JDBCUtils.getConn();
            String sql = "delete from student where id = ?";
            ps = conn.prepareStatement(sql);
            ps.setInt(1,6);

            int i = ps.executeUpdate();
            if(i > 0){
                System.out.println("删除成功");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeRes(conn,ps);
        }

    }

    //修改
    @Test
    public void update(){
        //模拟了一个从页面上传过来的数据
        Student student = new Student(2,"李二狗",45,"丽江",12,0);

        try {
            conn = JDBCUtils.getConn();
            String sql = "update student set username = ?,age = ?,address = ?,ordered = ?,status = ? where id = ? ";
            ps = conn.prepareStatement(sql);

            ps.setString(1,student.getUsername());
            ps.setInt(2,student.getAge());
            ps.setString(3,student.getAddress());
            ps.setInt(4,student.getOrdered());
            ps.setInt(5,student.getStatus());
            ps.setInt(6,student.getId());

            int i = ps.executeUpdate();
            if(i > 0){
                System.out.println("修改数据成功");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeRes(conn,ps);
        }

    }

    //查询全部数据
    @Test
    public void select(){
        try {
            //1.获得与数据库的连接
            conn = JDBCUtils.getConn();

            //2.书写执行sql语句
            String sql = "select * from student";

            //3.通过conn获取执行sql对象ps
            ps = conn.prepareStatement(sql);

            //3.1设置问号

            //4.执行sql并返回结果
             rs = ps.executeQuery();

            //5.将结果集中的数据进行操作获取为Java需要的数据对象
            List<Student> list = new ArrayList<>();

            //取出结果集中的数据 next() 1.光标下移 2.判断下移以后的行有无数据
            while(rs.next()){
                //循环取数据
                //根据结果集中的第几列取值 方法1
               /* int id = rs.getInt(1);
                String username = rs.getString(2);
                int age = rs.getInt(3);
                String address = rs.getString(4);
                int ordered = rs.getInt(5);
                int status = rs.getInt(6);*/

                //根据结果集中的列名来取值 方法
                int id = rs.getInt("id");
                String username = rs.getString("username");
                int age = rs.getInt("age");
                String address = rs.getString("address");
                int ordered = rs.getInt("ordered");
                int status = rs.getInt("status");

                Student student = new Student(id,username,age,address,ordered,status);
                //将每次得到的学生存储到集合中
                list.add(student);
            }
            System.out.println("list = " + list.toString());
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeRes(conn,ps,rs);
        }


    }

    //条件查询 根据id查询
    @Test
    public void selectById(){
        try {
            conn = JDBCUtils.getConn();
            String sql = "select * from student where id = ?";
            ps = conn.prepareStatement(sql);
            ps.setInt(1,1);
            rs = ps.executeQuery();

            if(rs.next()){
                //取值
                Student student = new Student(
                        rs.getInt(1),
                        rs.getString(2),
                        rs.getInt(3),
                        rs.getString(4),
                        rs.getInt(5),
                        rs.getInt(6)
                );
                System.out.println("student = " + student);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeRes(conn,ps,rs);
        }


    }




}

/*
* 创建表user
* 包含如下字段
* id  int  主键
* name varchar(20) 非空
* password varhcar(20)
*添加两条测试数据
* 使用jdbc+Sacnner模拟登录的效果   若用户名密码与数据库对应则登录成功
*
*
* */









